﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Diagnostics;
using System.IO;
using System.Reflection;
using System.Text;
using static System.Windows.Forms.VisualStyles.VisualStyleElement.TextBox;

namespace KD.Sms.DAL.SqlLite
{
    internal sealed partial class Main
    {
        #region 私有成员变量
        private string _strSql;
        /// <summary>
        /// 本类的唯一实例
        /// </summary>
        private static Main OWNER = null;

        #endregion

        /// <summary>
        /// 私有构造函数
        /// </summary>
        private Main()
        {
        }

        /// <summary>
        /// 获取类的唯一实例
        /// </summary>
        /// <returns></returns>
        public static Main Instance
        {
            get
            {
                if (Main.OWNER == null)
                {
                    Main.OWNER = new Main();
                }
                return Main.OWNER;
            }
        }

        /// <summary>
        /// 将获取到的数据同步到本地
        /// </summary>
        /// <param name="modelInfo"></param>
        /// <returns></returns>
        public bool DoSyncBaseAndBatch(KD.DAS.Cliecnt.SamplingInfo modelInfo)
        {
            try
            {
                List<string> list = new List<string>();
                list.Add(string.Format("delete from S_Kind where Kind_Id='{0}'", modelInfo.En_Mine));
                list.Add(string.Format("delete from S_Material where Material_Id='{0}'", modelInfo.En_Materiel));
                list.Add(string.Format("delete from S_Supplier where Supplier_Id='{0}'", modelInfo.En_Supplier));
                list.Add(string.Format("delete from S_TrafficUnit where Unit_ID='{0}'", modelInfo.En_Transport));
                list.Add(string.Format("delete from S_Batch where Codes='{0}'", modelInfo.En_Batch));

                list.Add(string.Format("insert into S_Kind(Kind_Id,Kind_Name)values('{0}','{1}')", modelInfo.En_Mine, modelInfo.En_MineName));
                list.Add(string.Format("insert into S_Material(Material_Id,Material_Name)values('{0}','{1}')", modelInfo.En_Materiel, modelInfo.En_MaterielName));
                list.Add(string.Format("insert into S_Supplier(Supplier_Id,Supplier_Name)values('{0}','{1}')", modelInfo.En_Supplier, modelInfo.En_SupplierName));
                list.Add(string.Format("insert into S_TrafficUnit(Unit_ID,Unit_Name)values('{0}','{1}')", modelInfo.En_Transport, modelInfo.En_TransportName));
                list.Add(string.Format("insert into S_Batch(Codes,Kind_ID,Material_Id,Supplier_Id,Unit_Id)values('{0}','{1}','{2}','{3}','{4}')", modelInfo.En_Batch, modelInfo.En_Mine, modelInfo.En_Materiel, modelInfo.En_Supplier, modelInfo.En_Transport));
                SQLiteHelper.ExecSQLTrans(list);
                return true;
            }
            catch (Exception ex)
            {
                Log.LogHelper.DoWriteLogTypeSubitem(Log.LogType.Error, Log.LogSubitem.Sqlite, "DoSyncBaseAndBatch()");
                Log.LogHelper.DoWriteLogTypeSubitem(Log.LogType.Error, Log.LogSubitem.Sqlite, ex.ToString());
                return false;
            }
        }

        /// <summary>
        /// 清理数据库
        /// </summary>
        /// <returns></returns>
        public bool DoCleanDataBase()
        {
            try
            {
                List<string> list = new List<string>();
                list.Add("delete from S_Image");
                list.Add("delete from S_Samplings");
                list.Add("delete from S_Kind");
                list.Add("delete from S_Material");
                list.Add("delete from S_Supplier");
                list.Add("delete from S_TrafficUnit");
                list.Add("delete from S_Batch");
                list.Add("delete from SysLog");
                list.Add("delete from SysUser where User_Acct<>'admin'");
                list.Add("delete from SysSamp");
                list.Add("delete from SysPurview");
                list.Add("delete from SysFault");
                list.Add("update S_Pail set Threshold_Value = 20,Current_Value= 0,Threshold_Weight = 17,Current_Weight = 0,Codes = ''");
                SQLiteHelper.ExecSQLTrans(list);
                return true;
            }
            catch (Exception ex)
            {
                Log.LogHelper.DoWriteLogTypeSubitem(Log.LogType.Error, Log.LogSubitem.Sqlite, "DoCleanDataBase()");
                Log.LogHelper.DoWriteLogTypeSubitem(Log.LogType.Error, Log.LogSubitem.Sqlite, ex.ToString());
                return false;
            }
        }

        /// <summary>
        /// 收缩数据库
        /// </summary>
        public void DoContractBase()
        {
            try
            {
                SQLiteHelper.ExecuteNonQuery("VACUUM", null);
            }
            catch (Exception ex)
            {
                Log.LogHelper.DoWriteLogTypeSubitem(Log.LogType.Error, Log.LogSubitem.Sqlite, "DoContractBase()");
                Log.LogHelper.DoWriteLogTypeSubitem(Log.LogType.Error, Log.LogSubitem.Sqlite, ex.ToString());
            }
        }

        /// <summary>
        /// 获取样桶号
        /// </summary>
        /// <param name="batch"></param>
        /// <param name="samplingcount"></param>
        /// <returns></returns>
        public int DoGetPailForSampling(string batch, int samplingcount, int cacheposcount, decimal weight)
        {
            int _iPail = 0;
            var _strSql = string.Empty;
            try
            {
                DataSet ds = null;
                _strSql = string.Format(@"select Pail_id
                                          from (select *
                                                  from S_Pail
                                                 where Status = 0 and (Codes = '' or Codes is null)
                                                   and Threshold_Value >= Current_Value + {1} and
                                                       Threshold_Weight >= Current_Weight + {2}
                                                union
                                                select *
                                                  from S_Pail
                                                 where Status = 0 and Codes = '{0}'
                                                   and Threshold_Value >= Current_Value + {1} and
                                                       Threshold_Weight >= Current_Weight + {2}) a
                                         order by Codes desc, Current_Value desc, Pail_id asc limit 1", batch, (samplingcount + cacheposcount), weight);
                ds = SQLiteHelper.GetDataSet(_strSql);
                if (ds != null && ds.Tables.Count > 0 && ds.Tables[0] != null && ds.Tables[0].Rows.Count == 1)
                    _iPail = int.Parse(ds.Tables[0].Rows[0][0].ToString());
            }
            catch (Exception ex)
            {
                Log.LogHelper.DoWriteLogTypeSubitem(Log.LogType.Error, Log.LogSubitem.Sqlite, "DoGetPailForSampling()");
                Log.LogHelper.DoWriteLogTypeSubitem(Log.LogType.Error, Log.LogSubitem.Sqlite, _strSql);
                Log.LogHelper.DoWriteLogTypeSubitem(Log.LogType.Error, Log.LogSubitem.Sqlite, ex.ToString());
            }
            return _iPail;
        }

        /// <summary>
        /// 获取同物料、供应商最近的平均值
        /// </summary>
        /// <param name="material"></param>
        /// <param name="supplier"></param>
        /// <returns></returns>
        public decimal DoGetSampleWeight(string material, string supplier)
        {
            try
            {
                _strSql = string.Format(@"select case
                                             when avgWeight is null then
                                              1.5
                                             else
                                              avgWeight
                                           end
                                      from (select ROUND(avg(SampleWeight), 2) as avgWeight
                                              from (select SampleWeight
                                                      from S_Samplings
                                                     where Material_Id = '{0}'
                                                       and Supplier_Id = '{1}'
                                                       and SampleWeight > 1
                                                     order by Sampling_Id desc limit 5) a        
                                            ) aa
                                    ", material, supplier);
                DataTable _tabSource = SQLiteHelper.GetDataTable(_strSql);
                return Convert.ToDecimal(_tabSource.Rows[0][0].ToString());
            }
            catch
            {
                return (decimal)1.8;
            }
        }

        /// <summary>
        /// 插入开始采样
        /// </summary>
        /// <param name="_modelInfo"></param>
        /// <param name="_listPost"></param>
        /// <returns></returns>
        public bool DoInsertSamplingBegin(Entity.SamplingInfo _modelInfo, List<Entity.SamplingPos> _listPost, out int _samplingid)
        {
            _samplingid = 0;
            try
            {
                //吴幽 修改  插入表 s_samplings 新增了 Sparestr1 字段
                List<string> list = new List<string>();
                _strSql = string.Format(@"insert into s_samplings(sampling_id,car_no,samplingcode,sample_pail,Pos_count,Kind_id,Material_id,Supplier_Id,Unit_id,Sampling_Type,LayoutType,TotalLength,CarriageLength,CarriageWidth,CarriageHeight,CarriagePostions,Sampling_User,Sparestr1)
                                        values(IFNULL((SELECT MAX(Sampling_Id) FROM S_Samplings), 0) + 1,'{0}','{1}',{2},{3},'{4}','{5}','{6}','{7}',0,{8},{9},{10},{11},{12},'{13}','{14}','{15}')",
                                         _modelInfo.En_Car, _modelInfo.En_Batch, _modelInfo.En_SamplingPail, _modelInfo.En_SamplingPosCount, _modelInfo.En_Mine, _modelInfo.En_Materiel,
                                         _modelInfo.En_Supplier, _modelInfo.En_Transport, _modelInfo.En_SamplingLayout, _modelInfo.En_TotleLength, _modelInfo.En_Length, _modelInfo.En_Width,
                                         _modelInfo.En_Height, _modelInfo.En_Postions, CacheUser.Chinese_name, _modelInfo.En_Sparestr1);
                list.Add(_strSql);
                int i = 0;
                foreach (Entity.SamplingPos pos in _listPost)
                {
                    i++;
                    _strSql = string.Format(@"INSERT INTO S_Pos 
                                            (Pos_Id, 
                                            Sampling_Id, Pos_NO, Block_NO,
                                            X, Y, Z)
                                            VALUES(IFNULL((SELECT MAX(Pos_Id) FROM S_Pos), 0) + 1,
                                            (SELECT MAX(Sampling_Id) FROM s_samplings),
                                            {0},{1},
                                            {2},{3},{4})", i, pos.BlockNumber, pos.X, pos.Y, pos.Z);
                    list.Add(_strSql);
                }
                _strSql = string.Format("update S_Pail set Codes = '{0}' where Pail_id= {1}", _modelInfo.En_Batch, _modelInfo.En_SamplingPail);
                list.Add(_strSql);

                SQLiteHelper.ExecSQLTrans(list);
                //获取最大id
                _samplingid = DoGetCurrentSamplingId();
                SQLiteHelper.ExecuteNonQuery(string.Format("update S_CarBatch set Sampling_id = {1} where Status = 0 and CarNum = '{0}'", _modelInfo.En_Car, _samplingid), null);
                return true;
            }
            catch (Exception ex)
            {
                Log.LogHelper.DoWriteLogTypeSubitem(Log.LogType.Error, Log.LogSubitem.Sqlite, "DoCleanDataBase()");
                Log.LogHelper.DoWriteLogTypeSubitem(Log.LogType.Error, Log.LogSubitem.Sqlite, ex.ToString());
                return false;
            }
        }

        /// <summary>
        /// 插入开始采样
        /// </summary>
        /// <param name="_modelInfo"></param>
        /// <param name="_listPost"></param>
        /// <returns></returns>
        public bool DoInsertSamplingPosForTest(List<Entity.SamplingPos> _listPost)
        {
            try
            {
                List<string> list = new List<string>();
                int i = 0;
                foreach (Entity.SamplingPos pos in _listPost)
                {
                    i++;
                    _strSql = string.Format(@"INSERT INTO S_Pos 
                                            (Pos_Id, 
                                            Sampling_Id, Pos_NO, Block_NO,
                                            X, Y, Z)
                                            VALUES(IFNULL((SELECT MAX(Pos_Id) FROM S_Pos), 0) + 1,
                                            1,
                                            {0},{1},
                                            {2},{3},{4})", i, pos.BlockNumber, pos.X, pos.Y, pos.Z);
                    list.Add(_strSql);
                }
                SQLiteHelper.ExecSQLTrans(list);
                return true;
            }
            catch (Exception ex)
            {
                Log.LogHelper.DoWriteLogTypeSubitem(Log.LogType.Error, Log.LogSubitem.Sqlite, "DoCleanDataBase()");
                Log.LogHelper.DoWriteLogTypeSubitem(Log.LogType.Error, Log.LogSubitem.Sqlite, ex.ToString());
                return false;
            }
        }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="material"></param>
        /// <param name="supplierid"></param>
        /// <returns></returns>
        public int DoQuerySubdivisionForSamplings(string material, string supplierid)
        {
            int _iValue = 0;
            _strSql = string.Format(@"select  Subdivision from(
                                        select * from S_Samplings where Material_ID='{0}' and Supplier_ID = '{1}' and Sampling_State = 1   and Sampling_time >= datetime(datetime(),'-180 day') order by Sampling_ID desc
                                        )a limit 1", material, supplierid);
            DataTable _tabSource = SQLiteHelper.GetDataTable(_strSql);
            if (_tabSource != null && _tabSource.Rows.Count == 1 && _tabSource.Rows[0][0] != DBNull.Value)
            {
                _iValue = int.Parse(_tabSource.Rows[0][0].ToString());
            }
            return _iValue;
        }

        /// <summary>
        /// 获取采样完成记录用于强制数据上传
        /// </summary>
        /// <returns></returns>
        public DataTable DoQuerySamplingsForUpdateWl(string _strCarno)
        {
            _strSql = string.Format(@"select Car_No,Sampling_Time,CardNo,'采样完成' AS CYWC
                                    from (select Car_No, Sampling_Time, b.CardNo
                                            from S_Samplings a
                                            left join S_CarInfo b
                                            on a.Car_no = b.CarNum
                                            where Sampling_State = 1 {0}
                                            order by Sampling_ID desc) a limit 10", string.Format(" and Car_No like '%{0}%'", _strCarno));
            return SQLiteHelper.GetDataTable(_strSql);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public int DoGetCurrentSamplingId()
        {
            DataTable _tabSource = new DataTable();

            _strSql = "SELECT MAX(Sampling_Id) AS Sampling_Id FROM S_Samplings";
            _tabSource = SQLiteHelper.GetDataTable(_strSql);
            if (_tabSource != null && _tabSource.Rows.Count > 0)
                return Convert.ToInt32(_tabSource.Rows[0]["Sampling_Id"]);
            else
                return 0;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="msImage"></param>
        /// <param name="isBegin"></param>
        /// <returns></returns>
        public bool DoInsertImage(System.IO.MemoryStream msImage, bool isBegin, int samplingid)
        {
            try
            {
                _strSql = string.Format(@"INSERT INTO S_Image 
                                    (Image_Id, Sampling_Id, Image_Data,Image_Type) 
                                    VALUES(IFNULL((SELECT MAX(Image_Id) FROM S_Image), 0) + 1,
                                    {0}, @Image_Data,{1})", samplingid, Convert.ToInt32(isBegin));
                SQLiteParameter[] prms = new SQLiteParameter[1];
                prms[0] = new SQLiteParameter(DbType.Binary, msImage.Length);
                prms[0].ParameterName = "@Image_Data";
                prms[0].Value = msImage.ToArray();
                SQLiteHelper.ExecuteNonQuery(_strSql, prms);
                return true;
            }
            catch (Exception ex)
            {
                Log.LogHelper.DoWriteLogTypeSubitem(Log.LogType.Error, Log.LogSubitem.Sqlite, "DoInsertImage()");
                Log.LogHelper.DoWriteLogTypeSubitem(Log.LogType.Error, Log.LogSubitem.Sqlite, ex.ToString());
                return false;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="msImage"></param>
        /// <param name="isBegin"></param>
        /// <returns></returns>
        public bool DoInsertImages(string msImagepath, bool isBegin, int samplingid)
        {
            try
            {
                if (isBegin)
                    _strSql = string.Format("insert into S_Images(Sampling_Id,Img_Begin)values({0},'{1}')", samplingid, msImagepath);
                else
                    _strSql = string.Format("update S_Images set Img_End = '{1}' where Sampling_Id={0} ", samplingid, msImagepath);
                SQLiteHelper.ExecuteNonQuery(_strSql, null);
                return true;
            }
            catch (Exception ex)
            {
                Log.LogHelper.DoWriteLogTypeSubitem(Log.LogType.Error, Log.LogSubitem.Sqlite, "DoInsertImage()");
                Log.LogHelper.DoWriteLogTypeSubitem(Log.LogType.Error, Log.LogSubitem.Sqlite, ex.ToString());
                return false;
            }
        }

        /// <summary>
        /// 获取所有样桶数据用于监控
        /// </summary>
        public DataTable DoQueryPailForMonitor()
        {
            try
            {
                return SQLiteHelper.GetDataTable("select Pail_Id as PailId,CAST(Current_Value as char)||'/'||CAST(Threshold_Value as char) as CurrentValue,Codes,Current_weight as Weight from S_Pail where Status = 0");
            }
            catch (Exception ex)
            {
                Log.LogHelper.DoWriteLogTypeSubitem(Log.LogType.Error, Log.LogSubitem.Sqlite, "DoQueryPailForMonitor()");
                Log.LogHelper.DoWriteLogTypeSubitem(Log.LogType.Error, Log.LogSubitem.Sqlite, ex.ToString());
                return null;
            }
        }

        /// <summary>
        /// 作废一个样桶
        /// </summary>
        /// <param name="pailid"></param>
        /// <returns></returns>
        public bool DoGiveupPail(int pailid)
        {
            try
            {
                return SQLiteHelper.ExecuteNonQuery("update s_pail set Status = 1 where Pail_id = {0}", null) > 0;
            }
            catch (Exception ex)
            {
                Log.LogHelper.DoWriteLogTypeSubitem(Log.LogType.Error, Log.LogSubitem.Sqlite, "DoGiveupPail()");
                Log.LogHelper.DoWriteLogTypeSubitem(Log.LogType.Error, Log.LogSubitem.Sqlite, ex.ToString());
                return false;
            }
        }

        /// <summary>
        /// 收到采样完成信号，开始保存当前采样数据
        /// </summary>
        /// <param name="pailid"></param>
        /// <param name="poscount"></param>
        /// <param name="weight"></param>
        /// <param name="batch"></param>
        /// <returns></returns>
        public bool DoUpdateSamplingEnd(int samplingid, int pail)
        {
            try
            {
                List<string> list = new List<string>();
                list.Add(string.Format("update s_Samplings set Sampling_Time_End = datetime(CURRENT_TIMESTAMP,'localtime') ,Sampling_State = 1,Sample_Pail = {0} ,Subdivision = {2} where Sampling_Id = {1}", pail, samplingid, CacheSystemControl.E_ReductioncCurrent));
                list.Add(string.Format("update S_CarBatch set status = 1 where Sampling_id = '{0}'", samplingid));
                list.Add("update SysParam set Param_Value = Param_Value + 1 where Param_Name = 'WasteCurrent'");
                SQLiteHelper.ExecSQLTrans(list);
                return true;
            }
            catch (Exception ex)
            {
                Log.LogHelper.DoWriteLogTypeSubitem(Log.LogType.Error, Log.LogSubitem.Sqlite, "DoUpdateSamplingEnd()");
                Log.LogHelper.DoWriteLogTypeSubitem(Log.LogType.Error, Log.LogSubitem.Sqlite, ex.ToString());
                return false;
            }
        }

        /// <summary>
        /// 采样结束 更新采样记录
        /// </summary>
        /// <param name="pailid"></param>
        /// <param name="poscount"></param>
        /// <param name="weight"></param>
        /// <param name="batch"></param>
        /// <param name="rfid"></param>
        /// <param name="samplingid"></param>
        /// <returns></returns>
        public bool DoUpdateSamplingEndPail(int pailid, int poscount, decimal weight, string batch, string rfid, int samplingid)
        {
            try
            {
                List<string> list = new List<string>();
                _strSql = string.Format("update S_Samplings set PackingState = 1 where PackingState = 0 and Sampling_State  = 1 and Sampling_Time > '{0}'", DateTime.Now.AddDays(-3).ToString("yyyy-MM-dd HH:mm:ss"));
                list.Add(_strSql);
                _strSql = string.Format("update s_pail set current_value = current_value+{0},current_weight = current_weight +({1}),Codes = '{2}' where Pail_id = {3}", poscount, weight, batch, pailid);
                list.Add(_strSql);
                _strSql = string.Format("update S_Samplings set SampleWeight = {0},SampleRfid = '{1}',PackingState = 1 where sampling_id  = {2}", weight, rfid, samplingid);
                list.Add(_strSql);
                SQLiteHelper.ExecSQLTrans(list);
                return true;
            }
            catch (Exception ex)
            {
                Log.LogHelper.DoWriteLogTypeSubitem(Log.LogType.Error, Log.LogSubitem.Sqlite, "DoUpdatePail()");
                Log.LogHelper.DoWriteLogTypeSubitem(Log.LogType.Error, Log.LogSubitem.Sqlite, ex.ToString());
                return false;
            }
        }

        /// <summary>
        /// 采样结束 更新样桶
        /// </summary>
        /// <param name="pailid"></param>
        /// <param name="poscount"></param>
        /// <param name="weight"></param>
        /// <param name="batch"></param>
        /// <param name="rfid"></param>
        /// <param name="samplingid"></param>
        /// <returns></returns>
        public bool DoUpdateSamplingEndPailWeight(decimal weight, int samplingid)
        {
            try
            {
                SQLiteHelper.ExecuteNonQuery($"update S_Samplings set PackingState = 1 where sampling_id  = {samplingid}");
                if (weight <= (decimal)0.06)
                    return false;
                if (GetSamplingWeight(samplingid))
                    return true;
                return SQLiteHelper.ExecuteNonQuery(string.Format("update S_Samplings set SampleWeight = {0},PackingState = 1 where sampling_id  = {1}", weight, samplingid), null) > 0;
            }
            catch (Exception ex)
            {
                Log.LogHelper.DoWriteLogTypeSubitem(Log.LogType.Error, Log.LogSubitem.Sqlite, "DoUpdateSamplingEndPailWeight()");
                Log.LogHelper.DoWriteLogTypeSubitem(Log.LogType.Error, Log.LogSubitem.Sqlite, ex.ToString());
                return false;
            }
        }

        /// <summary>
        /// 获取已保存重量
        /// </summary>
        /// <param name="samplingid"></param>
        /// <returns></returns>
        public bool GetSamplingWeight(int samplingid)
        {
            try
            {
                return SQLiteHelper.ExecuteScalar($"SELECT COUNT(1) FROM S_Samplings WHERE sampling_id = {samplingid} AND SampleWeight > 0.2").ToIntEx() > 0;
            }
            catch (Exception ex)
            {
                Log.LogHelper.DoWriteLogTypeSubitem(Log.LogType.Error, Log.LogSubitem.Sqlite, $"编号：{samplingid},查询到重量大于0.2，无需更新重量数据");
                Log.LogHelper.DoWriteLogTypeSubitem(Log.LogType.Error, Log.LogSubitem.Sqlite, ex.ToString());
                return false;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="user"></param>
        /// <param name="password"></param>
        /// <returns></returns>
        public DataTable DoLogin(string user, string password)
        {
            try
            {
                return SQLiteHelper.GetDataTable(string.Format("select * from sysuser where User_Acct = '{0}' and User_Pwd = '{1}' ", user, password));
            }
            catch (Exception ex)
            {
                Log.LogHelper.DoWriteLogTypeSubitem(Log.LogType.Error, Log.LogSubitem.Sqlite, "DoLogin()");
                Log.LogHelper.DoWriteLogTypeSubitem(Log.LogType.Error, Log.LogSubitem.Sqlite, ex.ToString());
                return null;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="begin"></param>
        /// <param name="end"></param>
        /// <returns></returns>
        public DataTable DoQuerySamplingInfo(string begin, string end, string material, string supplier, string state)
        {
            try
            {
                _strSql = @"select 0 as Row_Number,Sampling_Id,Car_NO,SamplingCode,kind_name,Material_name,Supplier_name,Unit_Name,Sample_Pail,Pos_Count,Sampling_Type,Subdivision,Sampling_Time,Sampling_State,Sampling_Time_End
                         ,SampleWeight,SampleRfid,Sampling_User,SamplingPos,TotalLength,CarriageLength,CarriageWidth,CarriageHeight,CarriagePostions from v_Samplings where  1 =  1";
                if (!string.IsNullOrEmpty(begin))
                    _strSql += string.Format(" and Sampling_Time >= '{0}'", begin);
                if (!string.IsNullOrEmpty(end))
                    _strSql += string.Format(" and Sampling_Time <= '{0}'", end);
                //if (!string.IsNullOrEmpty(material))
                //    _strSql += string.Format(" and Material_id = '{0}'", material);
                //if (!string.IsNullOrEmpty(supplier))
                //    _strSql += string.Format(" and Supplier_Id = '{0}'", supplier);
                if (!string.IsNullOrEmpty(material))
                    _strSql += string.Format(" and Material_name like '%{0}%'", material);
                if (!string.IsNullOrEmpty(supplier))
                    _strSql += string.Format(" and Supplier_name like '%{0}%'", supplier);
                if (!string.IsNullOrEmpty(state))
                    _strSql += string.Format(" and Sampling_State = '{0}'", state);
                _strSql += " order by Sampling_id asc";
                return SQLiteHelper.GetDataTable(_strSql);
            }
            catch (Exception ex)
            {
                Log.LogHelper.DoWriteLogTypeSubitem(Log.LogType.Error, Log.LogSubitem.Sqlite, "DoQuerySamplingInfo()");
                Log.LogHelper.DoWriteLogTypeSubitem(Log.LogType.Error, Log.LogSubitem.Sqlite, ex.ToString());
                return null;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="begin"></param>
        /// <param name="end"></param>
        /// <returns></returns>
        public DataTable DoQuerySamplingInfoSimple(string begin, string end, string material, string supplier, string state)
        {
            try
            {
                _strSql = @"select 0 as Row_Number,Sampling_Id,car_no,SamplingCode, kind_Name, Material_Name,supplier_name,Unit_Name,Sample_Pail, Pos_count,Sampling_Type,Subdivision,Sampling_Time,
                            Sampling_State,Sampling_time_end,SampleWeight,Sampling_User, samplingPos,UploadStatus,UploadTime from v_Samplings where 1 =1";
                if (!string.IsNullOrEmpty(begin))
                    _strSql += string.Format(" and Sampling_Time >= '{0}'", begin);
                if (!string.IsNullOrEmpty(end))
                    _strSql += string.Format(" and Sampling_Time <= '{0}'", end);
                //if (!string.IsNullOrEmpty(material))
                //    _strSql += string.Format(" and Material_id = '{0}'", material);
                //if (!string.IsNullOrEmpty(supplier))
                //    _strSql += string.Format(" and Supplier_Id = '{0}'", supplier);
                if (!string.IsNullOrEmpty(material))
                    _strSql += string.Format(" and Material_name like '%{0}%'", material);
                if (!string.IsNullOrEmpty(supplier))
                    _strSql += string.Format(" and Supplier_name like '%{0}%'", supplier);
                if (!string.IsNullOrEmpty(state))
                    _strSql += string.Format(" and Sampling_State = '{0}'", state);
                _strSql += " order by Sampling_id asc";
                return SQLiteHelper.GetDataTable(_strSql);
            }
            catch (Exception ex)
            {
                Log.LogHelper.DoWriteLogTypeSubitem(Log.LogType.Error, Log.LogSubitem.Sqlite, "DoQuerySamplingInfo()");
                Log.LogHelper.DoWriteLogTypeSubitem(Log.LogType.Error, Log.LogSubitem.Sqlite, ex.ToString());
                return null;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="begin"></param>
        /// <param name="end"></param>
        /// <returns></returns>
        public DataTable DoQuerySamplingPosStatic(string begin, string end, string material, string supplier)
        {
            try
            {
                _strSql = string.Format(@"select Block_NO,count(1) as Pos_Count from(
                                                   select b.Material_Id,b.supplier_id,b.Sampling_Time,a.Block_NO from S_Pos a
                                                   inner join v_Samplings b
                                                   on a.[Sampling_Id] = b.Sampling_Id
                                                   and b.Sampling_State = '已采样'
                                            )a where 1 = 1 {0} {1} {2} {3}
                                            group by Block_NO
                                            order by Block_NO",
                                            string.IsNullOrEmpty(material) ? string.Empty : string.Format(" and Material_id = '{0}'", material),
                                            string.IsNullOrEmpty(supplier) ? string.Empty : string.Format(" and Supplier_ID = '{0}'", supplier),
                                            string.IsNullOrEmpty(begin) ? string.Empty : string.Format(" and Sampling_Time >= '{0}'", begin),
                                            string.IsNullOrEmpty(end) ? string.Empty : string.Format(" and Sampling_Time <= '{0}'", end));
                return SQLiteHelper.GetDataTable(_strSql);
            }
            catch (Exception ex)
            {
                Log.LogHelper.DoWriteLogTypeSubitem(Log.LogType.Error, Log.LogSubitem.Sqlite, "DoQuerySamplingInfo()");
                Log.LogHelper.DoWriteLogTypeSubitem(Log.LogType.Error, Log.LogSubitem.Sqlite, ex.ToString());
                return null;
            }
        }

        /// <summary>
        /// 提取图片
        /// </summary>
        /// <param name="samplingid"></param>
        /// <returns></returns>
        public DataTable DoQueryImgeBySamplingId(string samplingid)
        {
            try
            {
                return SQLiteHelper.GetDataTable(string.Format("select BeginImg,EndImg from v_Image where Sampling_id = {0}", samplingid));
            }
            catch (Exception ex)
            {
                Log.LogHelper.DoWriteLogTypeSubitem(Log.LogType.Error, Log.LogSubitem.Sqlite, "DoQueryImgesBySamplingId()");
                Log.LogHelper.DoWriteLogTypeSubitem(Log.LogType.Error, Log.LogSubitem.Sqlite, ex.ToString());
                return null;
            }
        }

        /// <summary>
        /// 提取图片
        /// </summary>
        /// <param name="samplingid"></param>
        /// <returns></returns>
        public DataTable DoQueryImgesBySamplingId(string samplingid)
        {
            try
            {
                return SQLiteHelper.GetDataTable(string.Format("select Img_Begin,Img_End from S_Images where Sampling_Id= {0}", samplingid));
            }
            catch (Exception ex)
            {
                Log.LogHelper.DoWriteLogTypeSubitem(Log.LogType.Error, Log.LogSubitem.Sqlite, "DoQueryImgesBySamplingId()");
                Log.LogHelper.DoWriteLogTypeSubitem(Log.LogType.Error, Log.LogSubitem.Sqlite, ex.ToString());
                return null;
            }
        }

        /// <summary>
        /// 根据采样ID 提取车牌
        /// </summary>
        /// <param name="samplingid"></param>
        /// <returns></returns>
        public DataTable DoQueryCarNoBySamplingId(string samplingid)
        {
            try
            {
                var _carData = SQLiteHelper.GetDataTable(string.Format("select Car_NO,Sampling_Time from S_Samplings where Sampling_Id= {0}", samplingid));
                //if (_carData != null)
                //    if (_carData.Rows.Count > 0)
                //       return _carData.Rows[0]["Car_NO"].ToStringEx();
                return _carData;
            }
            catch (Exception ex)
            {
                Log.LogHelper.DoWriteLogTypeSubitem(Log.LogType.Error, Log.LogSubitem.Sqlite, "DoQueryImgesBySamplingId()");
                Log.LogHelper.DoWriteLogTypeSubitem(Log.LogType.Error, Log.LogSubitem.Sqlite, ex.ToString());
                return null;
            }
        }

        /// <summary>
        /// 插入故障报警
        /// </summary>
        /// <param name="fault"></param>
        /// <returns></returns>
        public bool DoInsertFault(string fault)
        {
            try
            {
                _strSql = string.Format(@"insert into SysFault(Content)
                                            select '{0}'
                                            where not exists(select 1 from SysFault where Content = '{0}' and FaultTime < '2023-03-11 09:23:44')", fault, DateTime.Now.AddMinutes(1).ToString("yyyy-MM-dd HH:mm:ss"));
                return SQLiteHelper.ExecuteNonQuery(_strSql, null) > 0;
            }
            catch (Exception ex)
            {
                Log.LogHelper.DoWriteLogTypeSubitem(Log.LogType.Error, Log.LogSubitem.Sqlite, "DoGiveupPail()");
                Log.LogHelper.DoWriteLogTypeSubitem(Log.LogType.Error, Log.LogSubitem.Sqlite, ex.ToString());
                return false;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="content"></param>
        /// <param name="begin"></param>
        /// <param name="end"></param>
        /// <returns></returns>
        public DataTable DoQueryFault(string content, string begin, string end)
        {
            try
            {
                _strSql = "select FaultTimeDay,strftime('%H:%M:%S', begintime) as begintime,strftime('%H:%M:%S', endtime) as endtime,Content from v_SysFault where 1 = 1";
                if (!string.IsNullOrEmpty(content))
                    _strSql += string.Format(" and Content like '%{0}%'", content);
                _strSql += string.Format(" and begintime>= '{0}'", begin);
                _strSql += string.Format(" and begintime<= '{0}'", end);
                _strSql += " order by FaultTimeDay asc,begintime asc";
                return SQLiteHelper.GetDataTable(_strSql);
            }
            catch (Exception ex)
            {
                Log.LogHelper.DoWriteLogTypeSubitem(Log.LogType.Error, Log.LogSubitem.Sqlite, "DoQueryImgesBySamplingId()");
                Log.LogHelper.DoWriteLogTypeSubitem(Log.LogType.Error, Log.LogSubitem.Sqlite, ex.ToString());
                return null;
            }
        }

        #region 无人值守测试表维护
        /// <summary>
        /// 查询无人值守的列表
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public DataTable DoQueryUnattendedList(string CarNum)
        {
            _strSql = string.Format(@"select *,'修改' as XG,'删除' as SC from Test_CarInfo ");
            if (!string.IsNullOrEmpty(CarNum))
            {
                _strSql += string.Format("where CarNum like '%{0}%' ", CarNum);
            }
            return SQLiteHelper.GetDataTable(_strSql);
        }

        /// <summary>
        /// 新增无人值守参数
        /// </summary>
        /// <returns></returns>
        public bool DoAddUnattended(string CarNum, decimal TotalLength, decimal CLength, decimal CWidth, decimal CHeight, string Postion, decimal TotalWidth)
        {
            //List<string> list = new List<string>();
            _strSql = $"insert into Test_CarInfo(CarNum,TotalLength,CLength,CWidth,CHeight,Postion,TotalWidth)values('{CarNum}',{TotalLength},{CLength},{CWidth},{CHeight},'{Postion}',{TotalWidth})";
            //  list.Add(_strSql);
            return SQLiteHelper.ExecuteNonQuery(_strSql, null) > 0;
            //return true;
        }

        /// <summary>
        /// 修改无人值守的参数
        /// </summary>
        /// <returns></returns>
        public bool DoUpdateUnattended(string CarNum, decimal TotalLength, decimal CLength, decimal CWidth, decimal CHeight, string Postion, decimal TotalWidth)
        {
            _strSql = $"update Test_CarInfo Set TotalLength={TotalLength},CLength = {CLength},CWidth={CWidth},CHeight={CHeight},Postion='{Postion}',TotalWidth={TotalWidth} where CarNum = '{CarNum}'";
            return SQLiteHelper.ExecuteNonQuery(_strSql, null) > 0;
        }

        /// <summary>
        /// 删除无人值守的参数
        /// </summary>
        /// <returns></returns>
        public bool DoDeleteUnattended(string CarNum)
        {
            _strSql = string.Format("delete from Test_CarInfo where CarNum='{0}'", CarNum);
            return SQLiteHelper.ExecuteNonQuery(_strSql, null) > 0;
        }

        #endregion
    }
}
